package model;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import utils.JDBCUtils;

public class StudentDaoJDBC implements StudentDao{

	public boolean writeStudent(Student student) {
		Connection conn = null;
		PreparedStatement pstat = null;
		String sql = "insert into students(name,gender,age,birthdate) "
				+ "values('"+student.getName()+
				"','"+student.getGender()+"',"+student.getAge()+",'"
				+student.getBirthDate()+"')";//表中有id和name这列
		int result = -1;
		//System.out.println(sql);
		try {
			conn = JDBCUtils.getConnection();
			pstat = conn.prepareStatement(sql);
			result = pstat.executeUpdate(sql);
			
		}catch (SQLException sqle) {
			sqle.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtils.realeaseAll(null,pstat, conn);
		}
		return result>0?true:false;
	}

	@Override
	public List<Student> readStudentByName(String name) {
		List<Student> stuList = new ArrayList<>();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		String sql = "select * from students where name like ?";
		Student student = null;
		try {
			conn = JDBCUtils.getConnection();
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, name);
			rs = pstat.executeQuery();
			while(rs.next()){
				String className = rs.getString("class");
				String stuNo = rs.getString("stuno");
				String stuName = rs.getString("name");
				String stuGender = rs.getString("gender");
				int stuAge = rs.getInt("age");
				String birthDate = rs.getString("birthdate");
				student = new Student(className,stuName,stuGender,stuAge,birthDate);
				student.setStuno(stuNo);
				stuList.add(student);
			}
		}catch (SQLException sqle) {
			sqle.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtils.realeaseAll(rs,pstat, conn);
		}
		return stuList;
	}
	public Student readStudentByStuno(String stuno) {
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		String sql = "select * from students where stuno like ?";
		Student student = null;
		try {
			conn = JDBCUtils.getConnection();
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, stuno);
			rs = pstat.executeQuery();
			while(rs.next()){
				String className = rs.getString("class");
				String stuNo = rs.getString("stuno");
				String stuName = rs.getString("name");
				String stuGender = rs.getString("gender");
				int stuAge = rs.getInt("age");
				String birthDate = rs.getString("birthdate");
				student = new Student(className,stuName,stuGender,stuAge,birthDate);
				student.setStuno(stuNo);
			}
		}catch (SQLException sqle) {
			sqle.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtils.realeaseAll(rs,pstat, conn);
		}
		return student;
	}
	public List<Student> getAllStudents() {
		Connection conn = null;
		Statement stat = null;
		ResultSet rs = null;
		Student student = null;
		List<Student> stuList = new ArrayList<>();
		String sql = "select * from students";
		try {
			conn = JDBCUtils.getConnection();
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);
			while (rs.next()) {
				String className = rs.getString("class");
				String stuNo = rs.getString("stuno");
				String stuName = rs.getString("name");
				String stuGender = rs.getString("gender");
				int stuAge = rs.getInt("age");
				String birthDate = rs.getString("birthdate");
				student = new Student(className,stuName,stuGender,stuAge,birthDate);
				student.setStuno(stuNo);
				stuList.add(student);
			}
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.realeaseAll(rs, stat, conn);
		}
		 return stuList;
	}

	@Override
	public void diplayAllStudent() {
		
	}


	@Override
	public Student readStudent(String stuno, String name) {
		return readStudentByStuno(stuno);
	}

	@Override
	public boolean deleteStudent(Student student) {
		Connection conn = null;
		PreparedStatement pstat = null;
		
		String sql = "delete from students where stuno= "+student.getStuno();
		int result = -1;
		System.out.println(sql);
		try {
			conn = JDBCUtils.getConnection();
			pstat = conn.prepareStatement(sql);
			//pstat.setString(1, student.getStuno());
			
			result = pstat.executeUpdate(sql);
			
		}catch (SQLException sqle) {
			sqle.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtils.realeaseAll(null,pstat, conn);
		}
		return result>0?true:false;
	}

	@Override
	public boolean change() {
		// TODO Auto-generated method stub
		return false;
	}

}
